package com.dx.mes.service.spc.service;


import com.dx.common.table.ppt.Spc_ret_chart_data;
import com.dx.common.table.ppt.Spc_ret_chart_raw;
import com.dx.mes.common.dao.IChartRawDao;
import com.dx.mes.common.dao.ISpcChartDataDao;
import com.dx.mes.common.transcoder.util.*;
import com.dx.mes.service.pojo.ChartDataConditionPo;
import com.dx.mes.service.pojo.ChartDataExcel;
import com.dx.mes.service.spc.tx.ChartData.ChartDataAndRawDataPo;
import com.dx.mes.service.spc.tx.ChartData.ChartDataInPo;
import com.dx.mes.service.spc.tx.ChartData.ChartDataOutPo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import java.io.IOException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.dx.mes.common.bean.def.GenericDef.*;

/**
 * @Title: SPCChartDataServcieImpl.java
 * @Package com.serviceimpl
 * @author Lynn.Sea
 * @date 2014-2-19 下午6:07:12
 * @version V1.0
 */
@Service("spcChartDataServiceImpl")
public class ChartDataServcieImpl implements IChartDataService {
	private LogUtils logUtils;

    @Autowired
    private IChartRawDao chartRawDao;
    @Autowired
    private SessionFactory sessionFactory;
    @Autowired
	private ISpcChartDataDao spcChartDataDao;
    
    public String subMainProc(String evt_no,String strInMsg) {
		AppContext.clear();
		AppContext.setCurrServiceName(ChartDataServcieImpl.class.getSimpleName());
		AppContext.setCurrEventNumber(evt_no);
		logUtils = new LogUtils(ChartDataServcieImpl.class);
		logUtils.info("[InTrx:" + strInMsg + "]");
		
		String strOutMsg = "";
		Session session = spcChartDataDao.getCrSession();
		ChartDataInPo inTrx = new ChartDataInPo();
		ChartDataOutPo outTrx = new ChartDataOutPo();
		outTrx.setTrx_id("XPCCHTDT");
		outTrx.setType_id("O");
		try {
			long start = System.currentTimeMillis();
			inTrx = JacksonUtil.fromJson(strInMsg, ChartDataInPo.class);
			char action_flg = inTrx.getAction_flg().charAt(0);
			switch( action_flg ){
				case ACTION_FLG_QUERY   :  getChartData(inTrx, outTrx)  ; break;   
				case 'L' : listChartData(inTrx, outTrx) ; break;
				case 'D' : distinctCondition(inTrx, outTrx) ; break;
				case 'A' : addComment(inTrx, outTrx); break;
				default  : errActionFnc(inTrx, outTrx);   
			}
            long end = System.currentTimeMillis();
            System.out.println("查询时间：" + (end-start));
            if( RETURN_CODE_OK.equals(outTrx.getRtn_code()) ){
				session.flush();
				outTrx.setRtn_code(RETURN_CODE_OK);
				outTrx.setRtn_mesg(RETURN_MESG_OK);
			}else{
				TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
			}
		} catch (Exception ex) {
			outTrx.setRtn_code("0000001");
			outTrx.setRtn_msg(ex.getMessage());
			TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
		} finally {
			strOutMsg = JacksonUtil.toJSONStr(outTrx);
			logUtils.info("[OutTrx:" + strOutMsg + "]");
		}
		return strOutMsg;

	}
    public void getChartData(ChartDataInPo inTrx, ChartDataOutPo outTrx) {
        List<Spc_ret_chart_data> spcRetChartDataList = new ArrayList<Spc_ret_chart_data>();
        try {
            Spc_ret_chart_data spc_ret_chart_data = getSPCChartData(inTrx.getCol_typ(),
                    inTrx.getGrp_no(), inTrx.getChart_no(), inTrx.getChart_typ(), inTrx.getInput_time_d());
            spcRetChartDataList.add(spc_ret_chart_data);
            outTrx.setOary(spcRetChartDataList);
            outTrx.setTbl_cnt(1);
            outTrx.setRtn_code(RETURN_CODE_OK);
            outTrx.setRtn_mesg(RETURN_MESG_OK);
        } catch (Exception e) {
            outTrx.setRtn_code(RETURN_CODE_UNKNOWN);
            outTrx.setRtn_mesg(LogUtil.stackTraceToString(e));
            return;
        }
    }

    public void listChartData(ChartDataInPo inTrx, ChartDataOutPo outTrx) throws Exception {
        int maxRowCnt = inTrx.getCount();//200
        String firstFlg = inTrx.getFirst_flg();
        List<Spc_ret_chart_data> spcRetChartDataList = new LinkedList<Spc_ret_chart_data>();
        try {
            if("Y".equals(firstFlg)){
                Date date=new Date();//取时间
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(calendar.DATE,-1);//
                date = calendar.getTime(); //
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                String dateString = formatter.format(date);

                Timestamp nowTime = Util.getCurrentTimestamp();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                inTrx.setInput_timestamp_from(dateString + " 08:00:00");
                inTrx.setInput_timestamp_to(sdf.format(nowTime));
                maxRowCnt = -1;
            }


            spcRetChartDataList = listSPCChartData(inTrx,maxRowCnt);
            int chartDataCnt = spcRetChartDataList.size();
            if (chartDataCnt > 0) {
            	Spc_ret_chart_data firstSpcChartData = spcRetChartDataList.get(0);
                if (firstSpcChartData.getId().getChart_typ_fk().equals(CHART_TYP_MEAN)//平均数
                        || firstSpcChartData.getId().getChart_typ_fk().equals(CHART_TYP_MEDIAN)) {//中位数

                    List<Long> dTimeList = new LinkedList<Long>();
                    for (Spc_ret_chart_data chartData : spcRetChartDataList) {
                        dTimeList.add(chartData.getId().getInput_time_d());
                    }
                }
            }
            List<Spc_ret_chart_data> oaryList = new LinkedList<Spc_ret_chart_data>();

            for (int i = chartDataCnt; i > 0; i--) {
                oaryList.add(spcRetChartDataList.get(i - 1));
            }

            List<ChartDataAndRawDataPo>  oaryList2 =  madeChartRawOut(inTrx,spcRetChartDataList);
            outTrx.setOary(oaryList);
            outTrx.setOary2(oaryList2);
            outTrx.setTbl_cnt(chartDataCnt);
            outTrx.setRtn_code(RETURN_CODE_OK);
            outTrx.setRtn_mesg(RETURN_MESG_OK);
        } catch (Exception e) {
            outTrx.setRtn_code(RETURN_CODE_UNKNOWN);
            outTrx.setRtn_mesg(LogUtil.stackTraceToString(e));
            e.printStackTrace();
            return;
        }
    }

    //add by xxd
    public List<ChartDataAndRawDataPo> madeChartRawOut(ChartDataInPo inTrx, List<Spc_ret_chart_data> spcRetChartDataList){
        List<Spc_ret_chart_raw> spcRetChartRawList = new LinkedList<Spc_ret_chart_raw>();
        List<ChartDataAndRawDataPo>  newChartRaws = new LinkedList<ChartDataAndRawDataPo>();
        if(spcRetChartDataList.size() > 0){
            for(int i = 0; i < spcRetChartDataList.size() ; i++){
                ChartDataAndRawDataPo newChartRaw = new ChartDataAndRawDataPo();
                //find raw data
                String colTyp = inTrx.getCol_typ();
                String grpNo = inTrx.getGrp_no();
                String chartNo = inTrx.getChart_no();
                String subGrpNoFk ="";
                subGrpNoFk = spcRetChartDataList.get(i).getSub_grp_no_fk();
                Long InputTimeD = spcRetChartDataList.get(i).getId().getInput_time_d();
                //InputTimeD =  spcRetChartDataList.get(i).getId().getInput_time_d();
                String hql = "From Spc_ret_chart_raw where 1=1";
                if(!StringUtils.isEmpty(colTyp)){
                    hql = hql + " and col_typ_fk = '" + colTyp + "'";
                }
                if(!StringUtils.isEmpty(grpNo)){
                    hql = hql + " and grp_no_fk = '" + grpNo + "'";
                }
                if(!StringUtils.isEmpty(chartNo)){
                    hql = hql + " and chart_no_fk = '" + chartNo + "'";
                }
                if(!StringUtils.isEmpty(subGrpNoFk)){
                    hql = hql + " and sub_grp_no = '" + subGrpNoFk + "'";
                }
                hql = hql + " and proc_time_d =" + InputTimeD + "";
                hql = hql + " order by proc_time_d asc";
                spcRetChartRawList =  chartRawDao.find(hql);

                //newChartRaw.setId(spcRetChartDataList.get(i).getId());
                newChartRaw.setCol_typ_fk(spcRetChartDataList.get(i).getId().getCol_typ_fk());
                newChartRaw.setGrp_no_fk(spcRetChartDataList.get(i).getId().getGrp_no_fk());
                newChartRaw.setChart_no_fk(spcRetChartDataList.get(i).getId().getChart_no_fk());
                newChartRaw.setChart_typ_fk(spcRetChartDataList.get(i).getId().getCol_typ_fk());
                newChartRaw.setInput_time_d(spcRetChartDataList.get(i).getId().getInput_time_d());
                newChartRaw.setUnq_seq_id(spcRetChartDataList.get(i).getUnq_seq_id());
                if(spcRetChartDataList.get(i).getInput_timestamp() != null){
                    newChartRaw.setInput_timestamp(spcRetChartDataList.get(i).getInput_timestamp().toString());
                }
                if(spcRetChartDataList.get(i).getProc_timestamp() != null){
                    newChartRaw.setProc_timestamp(spcRetChartDataList.get(i).getProc_timestamp().toString());
                }
                newChartRaw.setSub_grp_no_fk(spcRetChartDataList.get(i).getSub_grp_no_fk());
                newChartRaw.setEvt_usr(spcRetChartDataList.get(i).getEvt_usr());
                newChartRaw.setPrd_seq_id_fk(spcRetChartDataList.get(i).getPrd_seq_id_fk());
                newChartRaw.setExd_flg(spcRetChartDataList.get(i).getExd_flg());
                newChartRaw.setChart_value(spcRetChartDataList.get(i).getChart_value());
                newChartRaw.setSample_size_fk(spcRetChartDataList.get(i).getSample_size_fk());
                newChartRaw.setChart_spec_rlt_fk(spcRetChartDataList.get(i).getChart_spec_rlt_fk());
                newChartRaw.setChart_rule_1_rlt_fk(spcRetChartDataList.get(i).getChart_rule_1_rlt_fk());
                newChartRaw.setChart_rule_2_rlt_fk(spcRetChartDataList.get(i).getChart_rule_2_rlt_fk());
                newChartRaw.setOoc_ocap_fk(spcRetChartDataList.get(i).getOoc_ocap_fk());
                newChartRaw.setOos_ocap_fk(spcRetChartDataList.get(i).getOos_ocap_fk());
                newChartRaw.setChart_total_action_fk(spcRetChartDataList.get(i).getChart_total_action_fk());
                newChartRaw.setChart_detail_action_fk(spcRetChartDataList.get(i).getChart_detail_action_fk());
                newChartRaw.setChart_usl_fk(spcRetChartDataList.get(i).getChart_usl_fk());
                newChartRaw.setChart_ucl_fk(spcRetChartDataList.get(i).getChart_ucl_fk());
                newChartRaw.setChart_uwl_fk(spcRetChartDataList.get(i).getChart_uwl_fk());
                newChartRaw.setChart_mean_fk(spcRetChartDataList.get(i).getChart_mean_fk());
                newChartRaw.setChart_lwl_fk(spcRetChartDataList.get(i).getChart_lwl_fk());
                newChartRaw.setChart_lcl_fk(spcRetChartDataList.get(i).getChart_lcl_fk());
                newChartRaw.setChart_lsl_fk(spcRetChartDataList.get(i).getChart_lsl_fk());
                newChartRaw.setMdl_id_fk(spcRetChartDataList.get(i).getMdl_id_fk());
                newChartRaw.setP_tool_id_fk(spcRetChartDataList.get(i).getP_tool_id_fk());
                newChartRaw.setP_recipe_id_fk(spcRetChartDataList.get(i).getP_recipe_id_fk());
                newChartRaw.setP_path_id_fk(spcRetChartDataList.get(i).getP_path_id_fk());
                newChartRaw.setP_ope_no_fk(spcRetChartDataList.get(i).getP_ope_no_fk());
                newChartRaw.setP_ope_id_fk(spcRetChartDataList.get(i).getP_ope_id_fk());
                newChartRaw.setP_proc_id(spcRetChartDataList.get(i).getP_proc_id());
                newChartRaw.setM_tool_id_fk(spcRetChartDataList.get(i).getM_tool_id_fk());
                newChartRaw.setM_recipe_id_fk(spcRetChartDataList.get(i).getM_recipe_id_fk());
                newChartRaw.setChart_comment_rsn(spcRetChartDataList.get(i).getChart_comment_rsn());
                newChartRaw.setChart_comment(spcRetChartDataList.get(i).getChart_comment());
                newChartRaw.setChart_comment_usr(spcRetChartDataList.get(i).getChart_comment_usr());
                if(spcRetChartDataList.get(i).getChart_comment_timestamp() !=null){
                    newChartRaw.setChart_comment_timestamp(spcRetChartDataList.get(i).getChart_comment_timestamp().toString());
                }
                newChartRaw.setWo_id_fk(spcRetChartDataList.get(i).getWo_id_fk());
                newChartRaw.setSo_id_fk(spcRetChartDataList.get(i).getSo_id_fk());
                newChartRaw.setLot_id_fk(spcRetChartDataList.get(i).getLot_id_fk());
                newChartRaw.setCus_id_fk(spcRetChartDataList.get(i).getCus_id_fk());
                newChartRaw.setRawDataList(spcRetChartRawList);
                newChartRaws.add(newChartRaw);
            }
        }

        return newChartRaws;
    }

    public void distinctCondition(ChartDataInPo inTrx, ChartDataOutPo outTrx) {
        StringBuffer sql = new StringBuffer(
                "SELECT distinct lot_id_fk,wo_id_fk,so_id_fk,cus_id_fk,ope_id_fk,tool_id_fk FROM SPC_RET_MFG_DATA ");
        sql.append(" WHERE col_typ_fk = ? and grp_no_fk = ? and chart_no_fk = ?");
        outTrx.setChartDataCondition(distinctChartCondition(
        		inTrx.getCol_typ(), inTrx.getGrp_no(), inTrx.getChart_no(), inTrx.getChart_typ()));
        outTrx.setRtn_code(RETURN_CODE_OK);
    }

    public Sheet createTempExcel(Workbook workbook, List<Spc_ret_chart_data> chartDataList) throws IOException, ParseException {
       Sheet sheet = workbook.createSheet("制程数据");
        //列宽度待优化和赋值类型待优化
        Row headRow = sheet.createRow(0);
        String[] headers = ChartDataExcel.getHeaders();
        for (int i = 0; i < headers.length; i++) {
            setCell(headRow, i, headers[i]);
        }
        for (int i = 0; i < chartDataList.size(); i++) {
            Spc_ret_chart_data chartData = chartDataList.get(i);
            Row row = sheet.createRow(i + 1);
            setCell(row, ChartDataExcel.INPUT_TIMESTAMP, chartData.getInput_timestamp());
            setCell(row, ChartDataExcel.PRD_SEQ_ID_FK, chartData.getPrd_seq_id_fk());
            setCell(row, ChartDataExcel.CHART_VALUE, chartData.getChart_value());
            setCell(row, ChartDataExcel.CHART_SPEC_RLT_FK, chartData.getChart_spec_rlt_fk());
            setCell(row, ChartDataExcel.CHART_USL_FK, chartData.getChart_usl_fk());
            setCell(row, ChartDataExcel.CHART_UCL_FK, chartData.getChart_ucl_fk());
            setCell(row, ChartDataExcel.CHART_MEAN_FK, chartData.getChart_mean_fk());
            setCell(row, ChartDataExcel.CHART_LCL_FK, chartData.getChart_lcl_fk());
            setCell(row, ChartDataExcel.CHART_LSL_FK, chartData.getChart_lsl_fk());

            setCell(row, ChartDataExcel.EVT_USR, chartData.getEvt_usr());
            setCell(row, ChartDataExcel.MDL_ID_FK, chartData.getMdl_id_fk());
            setCell(row, ChartDataExcel.P_TOOL_ID_FK, chartData.getP_tool_id_fk());
            setCell(row, ChartDataExcel.P_RECIPE_ID_FK, chartData.getP_recipe_id_fk());
            setCell(row, ChartDataExcel.P_PATH_ID_FK, chartData.getP_path_id_fk());
            setCell(row, ChartDataExcel.P_OPE_NO_FK, chartData.getP_ope_no_fk());
            setCell(row, ChartDataExcel.P_OPE_ID_FK, chartData.getP_ope_id_fk());
            setCell(row, ChartDataExcel.P_PROC_ID_FK, chartData.getP_proc_id());
            setCell(row, ChartDataExcel.CHART_COMMENT_RSN, chartData.getChart_comment_rsn());
            setCell(row, ChartDataExcel.CHART_COMMENT, chartData.getChart_comment());
            setCell(row, ChartDataExcel.CHART_COMMENT_USR, chartData.getChart_comment_usr());
            setCell(row, ChartDataExcel.CHART_COMMENT_TIMESTAMP, chartData.getChart_comment_timestamp());

        }
        return sheet;
    }

    private void setCell(Row row, int column, Object value) {
        Cell scoreCell = row.createCell(column);
        scoreCell.setCellValue(value == null ? "" : value.toString());
    }

    public void addComment(ChartDataInPo inTrx, ChartDataOutPo outTrx) {
        addChartComment(inTrx);
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_msg(RETURN_MESG_OK);
    }
    public Spc_ret_chart_data getSPCChartData(String col_typ, String grp_no, String chart_no, String chart_typ,
                                              Long input_time_d) {

        StringBuffer sql = new StringBuffer();
        sql.append("FROM Spc_ret_chart_data WHERE ");
        sql.append("col_typ_fk=:col_typ").append(" and grp_no_fk=:grp_no").append(" and chart_no_fk =:chart_no")
                .append(" and chart_typ_fk =:chart_typ").append(" input_time_d=:input_time_d");
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createQuery(sql.toString());
        query.setString("col_typ", col_typ);
        query.setString("grp_no", grp_no);
        query.setString("chart_no", chart_no);
        query.setString("chart_typ", chart_typ);
        query.setLong("input_time_d", input_time_d);

        return (Spc_ret_chart_data) query.uniqueResult();
    }


    public List<Spc_ret_chart_data> listSPCChartData(ChartDataInPo inTrx, int maxRowCnt) throws ParseException {
    	List<Spc_ret_chart_data> spc_ret_chart_data_list = new ArrayList<Spc_ret_chart_data>();
    	String hql ="FROM Spc_ret_chart_data WHERE 1=1";
    	if (inTrx.getCol_typ() != null) {
    		hql = hql +" and col_typ_fk='" +inTrx.getCol_typ() +"'";
        }
        if (inTrx.getGrp_no() != null) {
        	hql = hql +" and grp_no_fk='" +inTrx.getGrp_no() +"'";
        }
        if (inTrx.getChart_no() != null) {
        	hql = hql +" and chart_no_fk='" +inTrx.getChart_no() +"'";
        }
        if (inTrx.getChart_typ() != null) {
        	hql = hql +" and chart_typ_fk='" +inTrx.getChart_typ() +"'";
        }
        if (inTrx.getCus_id() != null) {
        	hql = hql +" and cus_id_fk='" +inTrx.getCus_id() +"'";
        }
        if (inTrx.getMdl_id() != null) {
        	hql = hql +" and mdl_id_fk='" +inTrx.getMdl_id() +"'";
        }
        if (inTrx.getWo_id() != null) {
        	hql = hql +" and wo_id_fk='" +inTrx.getWo_id() +"'";
        }
      /*  if (inTrx.getLot_id() != null) {
        	hql = hql +" and lot_id_fk='" +inTrx.getLot_id() +"'";
        }*/
        if (inTrx.getP_ope_id() != null) {
        	hql = hql +" and p_ope_id_fk='" +inTrx.getP_ope_id() +"'";
        }
        if (inTrx.getP_tool_id() != null) {
        	hql = hql +" and p_tool_id_fk='" +inTrx.getP_tool_id() +"'";
        }
        if(!StringUtils.isEmpty(inTrx.getLine_id_fk())){
            hql += " and line_id_fk =  '" + inTrx.getLine_id_fk() +"'";
        }
        if(!StringUtils.isEmpty(inTrx.getFab_id_fk())){
            hql += " and fab_id_fk = '" + inTrx.getFab_id_fk() +"'";
        }

        if(!StringUtils.isEmpty(inTrx.getShift_id_fk())){
            hql += " and shift_id_fk = '" + inTrx.getShift_id_fk() + "'";
        }

        if(!StringUtils.isEmpty(inTrx.getBoat_number())){
            hql += " and boat_number ='" + inTrx.getBoat_number() +"'";
        }

        if (inTrx.getInput_timestamp_from() != null && inTrx.getInput_timestamp_to() != null) {
            hql = hql +" and (input_timestamp between '" +
                    inTrx.getInput_timestamp_from() +"' and '"+ inTrx.getInput_timestamp_to()+"')";
        } else if (inTrx.getInput_timestamp_from() != null && inTrx.getInput_timestamp_to() == null) {
            hql = hql +" and (input_timestamp >='" + inTrx.getInput_timestamp_from() +"')";
        } else if (inTrx.getInput_timestamp_from() == null && inTrx.getInput_timestamp_to() != null) {
            hql = hql +" and (input_timestamp <='" + inTrx.getInput_timestamp_to() +"')";
        }
        hql = hql + " order by input_time_d asc";
//        if(maxRowCnt > 0){
//       /* 	hql = hql +" limit "+maxRowCnt;  //mysqsl专用*/
//            hql = hql + " fetch first " + maxRowCnt +" rows only";
//         }
        spc_ret_chart_data_list = spcChartDataDao.find(hql);
    	return spc_ret_chart_data_list;
    }

    public static void main(String[] args) {
        List<String> linkList = new LinkedList<String>();
        linkList.add("a");
        linkList.add("b");
        linkList.add("c");
        System.out.println(linkList);
        ListIterator<String> it = linkList.listIterator();
        while (it.hasNext()) {
            String a = it.next();
            if (a.equals("b")) {
                it.remove();
            }
            if (a.equals("c")) {
                it.remove();
            }
        }
        System.out.println(linkList);

    }

    public void addChartComment(ChartDataInPo inTrx) {
        String hql = "FROM Spc_ret_chart_data where col_typ_fk = :col_typ_fk and grp_no_fk = :grp_no_fk and chart_no_fk = :chart_no_fk ";
        hql = hql + " and input_time_d = :input_time_d";

        Session session = sessionFactory.getCurrentSession();

        Query query = session.createQuery(hql);
        query.setString("col_typ_fk", inTrx.getCol_typ());
        query.setString("grp_no_fk", inTrx.getGrp_no());
        query.setString("chart_no_fk", inTrx.getChart_no());
        query.setLong("input_time_d", inTrx.getInput_time_d());

        Spc_ret_chart_data spcRetChartData = (Spc_ret_chart_data) query.uniqueResult();

        if (spcRetChartData == null) {
            throw new RuntimeException("找不到SPC中对应的点，错误的输入");
        }

        StringBuffer hqlBuffer = new StringBuffer("update SPC_RET_CHART_DATA set chart_comment = '" + inTrx.getComment()
                + "' ");
        hqlBuffer.append(" where COL_TYP_FK = '").append(inTrx.getCol_typ()).append("' ");
        hqlBuffer.append(" and grp_no_fk = '").append(inTrx.getGrp_no()).append("'");
        hqlBuffer.append(" and chart_no_fk = '").append(inTrx.getChart_no()).append("'");
        hqlBuffer.append(" and input_time_d like ").append(inTrx.getInput_time_d());

        System.out.println(hqlBuffer.toString());
        SQLQuery sqlQuery = session.createSQLQuery(hqlBuffer.toString());

        sqlQuery.executeUpdate();
    }

    public ChartDataConditionPo distinctChartCondition(String col_typ, String grp_no, String chart_no, String chart_typ) {
        ChartDataConditionPo chartDataConditionPo = new ChartDataConditionPo();
        StringBuffer sql = new StringBuffer(
                "SELECT DISTINCT CUS_ID_FK, MDL_ID_FK,WO_ID_FK ,LOT_ID_FK,P_OPE_ID_FK,P_TOOL_ID_FK FROM SPC_RET_CHART_DATA ");
        sql.append(" WHERE COL_TYP_FK = '").append(col_typ).append("'");
        sql.append(" AND GRP_NO_FK = '").append(grp_no).append("'");
        sql.append(" AND CHART_NO_FK = '").append(chart_no).append("'");
        sql.append(" AND CHART_TYP_FK = '").append(chart_typ).append("'");
        Session session = sessionFactory.getCurrentSession();
        SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
        @SuppressWarnings("unchecked")
        List<Object[]> objList = sqlQuery.list();
        Map<String, Set<String>> cusMdlMap = new HashMap<String, Set<String>>();
        Map<String, Set<String>> mdlWoMap = new HashMap<String, Set<String>>();
        Map<String, Set<String>> woLotMap = new HashMap<String, Set<String>>();
        Map<String, Set<String>> opeToolMap = new HashMap<String, Set<String>>();

        Set<String> cusIdSet = new HashSet<String>();
        Set<String> mdlIdSet = new HashSet<String>();
        Set<String> woIdSet = new HashSet<String>();
        Set<String> lotIdSet = new HashSet<String>();
        Set<String> opeIdSet = new HashSet<String>();
        Set<String> toolIdSet = new HashSet<String>();

        for (Object[] objs : objList) {
            String cusIdFk = obj2String(objs[0]);
            String mdlIdFk = obj2String(objs[1]);
            String woIdFk = obj2String(objs[2]);
            String lotIdFk = obj2String(objs[3]);
            String opeIdFk = obj2String(objs[4]);
            String toolIdFk = obj2String(objs[5]);

            if (!StringUtils.isEmpty(cusIdFk)) {

                cusIdSet.add(cusIdFk);

                Set<String> tmpMdlIdSet = cusMdlMap.get(cusIdFk);
                tmpMdlIdSet = tmpMdlIdSet == null ? new HashSet<String>() : tmpMdlIdSet;

                if (!StringUtils.isEmpty(mdlIdFk)) {
                    tmpMdlIdSet.add(mdlIdFk);
                    cusMdlMap.put(cusIdFk, tmpMdlIdSet);
                }
            }
            if (!StringUtils.isEmpty(mdlIdFk)) {

                mdlIdSet.add(mdlIdFk);

                Set<String> tmpWoIdSet = mdlWoMap.get(mdlIdFk);
                tmpWoIdSet = tmpWoIdSet == null ? new HashSet<String>() : tmpWoIdSet;

                if (!StringUtils.isEmpty(woIdFk)) {
                    tmpWoIdSet.add(woIdFk);
                    mdlWoMap.put(mdlIdFk, tmpWoIdSet);
                }
            }
            if (!StringUtils.isEmpty(woIdFk)) {

                woIdSet.add(woIdFk);

                Set<String> tmpLotIdSet = woLotMap.get(woIdFk);
                tmpLotIdSet = tmpLotIdSet == null ? new HashSet<String>() : tmpLotIdSet;

                if (!StringUtils.isEmpty(lotIdFk)) {

                    tmpLotIdSet.add(lotIdFk);
                    woLotMap.put(woIdFk, tmpLotIdSet);
                }
            }

            if (!StringUtils.isEmpty(lotIdFk)) {
                lotIdSet.add(lotIdFk);
            }

            if (!StringUtils.isEmpty(opeIdFk)) {

                opeIdSet.add(opeIdFk);

                Set<String> tmpToolIdSet = opeToolMap.get(opeIdFk);
                tmpToolIdSet = tmpToolIdSet == null ? new HashSet<String>() : tmpToolIdSet;

                if (!StringUtils.isEmpty(toolIdFk)) {
                    tmpToolIdSet.add(toolIdFk);
                    opeToolMap.put(opeIdFk, tmpToolIdSet);
                }
            }

            if (!StringUtils.isEmpty(toolIdFk)) {
                toolIdSet.add(toolIdFk);
            }
        }

        chartDataConditionPo.setCusMdlMap(cusMdlMap);
        chartDataConditionPo.setMdlWoMap(mdlWoMap);
        chartDataConditionPo.setWoLotMap(woLotMap);
        chartDataConditionPo.setOpeToolMap(opeToolMap);
        chartDataConditionPo.setCusIdSet(cusIdSet);
        chartDataConditionPo.setMdlIdSet(mdlIdSet);
        chartDataConditionPo.setWoIdSet(woIdSet);
        chartDataConditionPo.setLotIdSet(lotIdSet);
        chartDataConditionPo.setOpeIdSet(opeIdSet);
        chartDataConditionPo.setToolIdSet(toolIdSet);

        return chartDataConditionPo;
    }

    private String obj2String(Object obj) {
        return obj == null ? null : obj.toString();
    }
    
    public void errActionFnc(ChartDataInPo inTrx, ChartDataOutPo outTrx){
		outTrx.setRtn_code("0000009");
		outTrx.setRtn_mesg("Invalid Action_flg");
	}
}
